﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class TeacherDAO : CommonDAO
    {
        //cn
        //sql
        private const string sql_getTeacherHornorById = "select HonorDesc from Teacher where TeacherID = @TeacherID";
        private const string sql_getTeachingDescById = "select TeachingDesc from Teacher where TeacherID = @TeacherID";
        private const string sql_getTeacherDescById = "select TeacherDesc from Teacher where TeacherID = @TeacherID";
        private const string sql_getResearchDescById = "select ResearchDesc from Teacher where TeacherID = @TeacherID";
        private const string sql_getTeacherById = "select TeacherId,TeacherName,Address,Telephone ,Fax ,Email ,ProfessionalTitle, Position, EducationDegree, ResearchArea, JobBackgroud, ImgPath  from Teacher where TeacherID = @TeacherID";
        private const string sql_getTeacherPlan = "select Teacher.TeacherID, TeacherName, ProfessionalTitle, ResearchArea, PlanNum, CurNum from Teacher inner join TeacherPlan on Teacher.TeacherID=TeacherPlan.TeacherID";
        private const string sql_updateTeacherResearch = "update Teacher set ResearchDesc = @ResearchDesc where TeacherID = @TeacherID";
        private const string sql_updateTeacherDesc = "update Teacher set TeacherDesc = @TeacherDesc where TeacherID = @TeacherID";
        private const string sql_updateTeacherHonor = "update Teacher set HonorDesc = @HonorDesc where TeacherID = @TeacherID";
        private const string sql_updateTeacherInfo = "update Teacher set TeacherName = @TeacherName, Address = @TeacherAddress, Telephone = @Telephone, Fax = @Fax, Email = @Email, ProfessionalTitle = @Title, EducationDegree = @Degree, ResearchArea = @ResearchArea, JobBackgroud = @Job where TeacherID = @TeacherID";
        private const string sql_updateTeacherTeaching = "update Teacher set TeachingDesc = @TeachingDesc where TeacherID = @TeacherID";
        private const string sql_updateTeacherImg = "update Teacher set ImgPath = @ImgPath where TeacherID = @TeacherID";
        private const string table_teacher = "Teacher";
        private const string sql_getAllTeacherInfoById = "select * from Teacher where TeacherID = @TeacherID";
        public TeacherDAO()
            : base()
        {

        }

        public DataSet getAllTeachersAndPlan()
        {
            return this.getInfoByCommand(sql_getTeacherPlan, "viewTeacherPlan");
        }

        public DataSet getTeacherAllInfoByID(string teacherId)
        {
            SqlCommand cmd = this.getCommand(sql_getAllTeacherInfoById);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = teacherId;
            return this.executeCommand(cmd, "viewTeacherInfo");
        }

        public DataSet getTeacherByID(string strTeacherID)
        {
            //private const string sql_getTeacherById = "select TeacherName,Address,Telephone ,Fax ,Email ,ProfessionalTitle ,Position , EducationDegree ,ResearchArea , JobBackgroud, ImgPath  from Teacher";
            SqlCommand cmd = this.getCommand(sql_getTeacherById);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = strTeacherID;
            return this.executeCommand(cmd, "viewTeacheriInfo");
        }



        public string getTeacherHornorByID(string TeacherID)
        {
            SqlCommand cmd = this.getCommand(sql_getTeacherHornorById);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = TeacherID;
            return this.executeCommand(cmd, "Teacher").Tables[0].Rows[0][0].ToString().Trim();
        }

        public string getTeachingDescByID(string TeacherID)
        {
            SqlCommand cmd = this.getCommand(sql_getTeachingDescById);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = TeacherID;
            return this.executeCommand(cmd, "Teacher").Tables[0].Rows[0][0].ToString().Trim();
        }

        public string getTeacherDescByID(string TeacherID)
        {
            SqlCommand cmd = this.getCommand(sql_getTeacherDescById);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = TeacherID;
            return this.executeCommand(cmd, "Teacher").Tables[0].Rows[0][0].ToString().Trim();
        }

        public string getResearchDescByID(string TeacherID)
        {
            SqlCommand cmd = this.getCommand(sql_getResearchDescById);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = TeacherID;
            return this.executeCommand(cmd, "Teacher").Tables[0].Rows[0][0].ToString().Trim();
        }


        public void updateHonorDesc(string teacherID, string content)
        {
            SqlCommand cmd = this.getCommand(sql_updateTeacherHonor);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = teacherID;
            cmd.Parameters.Add("@HonorDesc", SqlDbType.NText).Value = content;
            this.ExecuteNonQueryCommand(cmd);

        }

        public void updateResearchDesc(string teacherID, string content)
        {
            SqlCommand cmd = this.getCommand(sql_updateTeacherResearch);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = teacherID;
            cmd.Parameters.Add("@ResearchDesc", SqlDbType.NText).Value = content;
            this.ExecuteNonQueryCommand(cmd);

        }

        public void updateTeacherDesc(string teacherID, string content)
        {
            SqlCommand cmd = this.getCommand(sql_updateTeacherDesc);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = teacherID;
            cmd.Parameters.Add("@TeacherDesc", SqlDbType.NText).Value = content;
            this.ExecuteNonQueryCommand(cmd);

        }

        public void updateTeachingDesc(string teacherID, string content)
        {
            SqlCommand cmd = this.getCommand(sql_updateTeacherTeaching);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = teacherID;
            cmd.Parameters.Add("@TeachingDesc", SqlDbType.NText).Value = content;
            this.ExecuteNonQueryCommand(cmd);

        }

        public bool updateTeacherImg(string teacherID, string ImgPath)
        {
            SqlCommand cmd = this.getCommand(sql_updateTeacherImg);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = teacherID;
            cmd.Parameters.Add("@ImgPath", SqlDbType.NVarChar, 256).Value = ImgPath;
            try
            {
                this.ExecuteNonQueryCommand(cmd);
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }

        }

        public bool updateTeacherInfo(string teacherName, string teacherAddress, string teacherTelephone,
                                      string teacherFax, string teacherEmail, string teacherTitle, string teacherDegree,
                                      string teacherResearch, string teacherJob, string teacherId)
        {
            //private const string sql_updateTeacherInfo = "update Teacher set TeacherName = @TeacherName, Address = @TeacherAddress, Telephone = @Telephone, Fax = @Fax, Email = @Email, ProfessionalTitle = @Title, EducationDegree = @Degree, ResearchArea = @ResearchArea, JobBackgroud = @Job where TeacherID = @TeacherID";

            SqlCommand cmd = this.getCommand(sql_updateTeacherInfo);
            cmd.Parameters.Add("@TeacherID", SqlDbType.NChar, 10).Value = teacherId;
            cmd.Parameters.Add("@TeacherName", SqlDbType.NVarChar, 20).Value = teacherName;
            cmd.Parameters.Add("@TeacherAddress", SqlDbType.NVarChar, 100).Value = teacherAddress;
            cmd.Parameters.Add("@Telephone", SqlDbType.NVarChar, 15).Value = teacherTelephone;
            cmd.Parameters.Add("@Fax", SqlDbType.NVarChar, 15).Value = teacherFax;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 30).Value = teacherEmail;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 10).Value = teacherTitle;
            cmd.Parameters.Add("@Degree", SqlDbType.NVarChar, 10).Value = teacherDegree;
            cmd.Parameters.Add("@ResearchArea", SqlDbType.NVarChar, 20).Value = teacherResearch;
            cmd.Parameters.Add("@Job", SqlDbType.NVarChar, 500).Value = teacherJob;
            try
            {
                this.ExecuteNonQueryCommand(cmd);
                return true;
            }
            catch (System.Exception ex)
            {
                return false;
            }
        }
    }
}

